﻿using Dapper;
using Dapper.Contrib.Extensions;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace HSJM.Service.MWS.Infrastructure.Dapper
{
    public static class SQLLib
    {
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, string> TypeTableName = new ConcurrentDictionary<RuntimeTypeHandle, string>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> KeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> TypeProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> ExplicitKeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();

        /// <summary>
        /// sql体系
        /// </summary>
        public enum SQLDialect
        {
            SQLServerOld = 0,
            PostgreSQL = 1,
            SQLite = 2,
            MySQL = 3,
            SQLServerNew = 4,
        }

        /// <summary>
        /// 返回分页模板
        /// </summary>
        /// <param name="dialect"></param>
        /// <returns></returns>
        public static string GetPaginationTemplate(SQLDialect dialect)
        {
            switch (dialect)
            {
                case SQLDialect.PostgreSQL:
                    //_encapsulation = "\"{0}\"";
                    //_getIdentitySql = string.Format("SELECT LASTVAL() AS id");
                    return "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage});";
                //break;
                case SQLDialect.SQLite:
                    //_encapsulation = "\"{0}\"";
                    //_getIdentitySql = string.Format("SELECT LAST_INSERT_ROWID() AS id");
                    return "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage});";
                //break;
                case SQLDialect.MySQL:
                    //_encapsulation = "`{0}`";
                    //_getIdentitySql = string.Format("SELECT LAST_INSERT_ID() AS id");
                    return "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {Offset},{RowsPerPage};";
                //break;
                case SQLDialect.SQLServerNew:
                    return @"SELECT {SelectColumns} FROM {TableName} {WhereClause} ORDER BY {OrderBy} OFFSET {Offset} ROWS FETCH NEXT {RowsPerPage} ROWS ONLY;";
                default:
                    //_encapsulation = "[{0}]";
                    //_getIdentitySql = string.Format("SELECT CAST(SCOPE_IDENTITY()  AS BIGINT) AS [id]");
                    return "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {OrderBy}) AS PagedNumber, {SelectColumns} FROM {TableName} {WhereClause}) AS u WHERE PagedNumber BETWEEN (({PageNumber}-1) * {RowsPerPage} + 1) AND ({PageNumber} * {RowsPerPage});";
                    //break;
            }
        }

        /// <summary>
        /// 返回分页语句
        /// </summary>
        /// <param name="dialect"></param>
        /// <param name="TableName">表名</param>
        /// <param name="SelectColumns">查询列</param>
        /// <param name="pageNumber">页码</param>
        /// <param name="rowsPerPage">每页行数</param>
        /// <param name="conditions">查询条件</param>
        /// <param name="orderby">排序条件</param>
        /// <returns></returns>
        public static string PaginationSQL(SQLDialect dialect, string tableName, string selectColumns, int pageNumber, int rowsPerPage, string conditions, string orderby)
        {
            string query = GetPaginationTemplate(dialect);
            query = query.Replace("{SelectColumns}", selectColumns);//查询列
            query = query.Replace("{TableName}", tableName);//表名
            query = query.Replace("{PageNumber}", pageNumber.ToString());//页码
            query = query.Replace("{RowsPerPage}", rowsPerPage.ToString());//每页行数
            query = query.Replace("{OrderBy}", orderby);//排序条件
            query = query.Replace("{WhereClause}", conditions);//查询条件
            query = query.Replace("{Offset}", ((pageNumber - 1) * rowsPerPage).ToString());//偏移量
            return query;
        }

        /// <summary>
        /// 生成取总数的SQL
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public static string CountSQL(string tableName, string conditions = "", string countColumn = "*")
        {
            string query = "SELECT count({CountColumn}) FROM {TableName} {WhereClause};";
            query = query.Replace("{CountColumn}", countColumn);//列名
            query = query.Replace("{TableName}", tableName);//表名
            query = query.Replace("{WhereClause}", conditions);//查询条件
            return query;
        }


        /// <summary>
        /// 取类对应的表名
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string GetTableName(Type type)
        {
            if (TypeTableName.TryGetValue(type.TypeHandle, out string name)) return name;

            var tableAttrName =
                type.GetCustomAttribute<TableAttribute>(false)?.Name
                ?? (type.GetCustomAttributes(false).FirstOrDefault(attr => attr.GetType().Name == "TableAttribute") as dynamic)?.Name;

            if (tableAttrName != null)
            {
                name = tableAttrName;
            }
            else
            {
                name = type.Name + "s";
                if (type.IsInterface && name.StartsWith("I"))
                    name = name.Substring(1);
            }

            TypeTableName[type.TypeHandle] = name;
            return name;
        }

        public static PropertyInfo GetSingleKey<T>(string method)
        {
            var type = typeof(T);
            var keys = KeyPropertiesCache(type);
            var explicitKeys = ExplicitKeyPropertiesCache(type);
            var keyCount = keys.Count + explicitKeys.Count;
            if (keyCount > 1)
                throw new DataException($"{method}<T> only supports an entity with a single [Key] or [ExplicitKey] property. [Key] Count: {keys.Count}, [ExplicitKey] Count: {explicitKeys.Count}");
            if (keyCount == 0)
                throw new DataException($"{method}<T> only supports an entity with a [Key] or an [ExplicitKey] property");

            return keys.Count > 0 ? keys[0] : explicitKeys[0];
        }

        private static List<PropertyInfo> KeyPropertiesCache(Type type)
        {
            if (KeyProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pi))
            {
                return pi.ToList();
            }

            var allProperties = TypePropertiesCache(type);
            var keyProperties = allProperties.Where(p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute)).ToList();

            if (keyProperties.Count == 0)
            {
                var idProp = allProperties.Find(p => string.Equals(p.Name, "id", StringComparison.CurrentCultureIgnoreCase));
                if (idProp != null && !idProp.GetCustomAttributes(true).Any(a => a is ExplicitKeyAttribute))
                {
                    keyProperties.Add(idProp);
                }
            }

            KeyProperties[type.TypeHandle] = keyProperties;
            return keyProperties;
        }
        private static List<PropertyInfo> TypePropertiesCache(Type type)
        {
            if (TypeProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pis))
            {
                return pis.ToList();
            }

            var properties = type.GetProperties().Where(IsWriteable).ToArray();
            TypeProperties[type.TypeHandle] = properties;
            return properties.ToList();
        }
        private static bool IsWriteable(PropertyInfo pi)
        {
            var attributes = pi.GetCustomAttributes(typeof(WriteAttribute), false).AsList();
            if (attributes.Count != 1) return true;

            var writeAttribute = (WriteAttribute)attributes[0];
            return writeAttribute.Write;
        }
        private static List<PropertyInfo> ExplicitKeyPropertiesCache(Type type)
        {
            if (ExplicitKeyProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pi))
            {
                return pi.ToList();
            }

            var explicitKeyProperties = TypePropertiesCache(type).Where(p => p.GetCustomAttributes(true).Any(a => a is ExplicitKeyAttribute)).ToList();

            ExplicitKeyProperties[type.TypeHandle] = explicitKeyProperties;
            return explicitKeyProperties;
        }
    }

    public static class Extensions
    {
        /// <summary>
        /// 生成取总数的SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="entity"></param>
        /// <param name="tableName"></param>
        /// <param name="conditions"></param>
        /// <param name="countColumn"></param>
        /// <returns></returns>
        public static int Count<T>(this IDbConnection connection, T entity, string tableName = "", string conditions = "", string countColumn = "*")
        {
            if (string.IsNullOrEmpty(tableName))
            {
                var type = typeof(T);
                tableName = SQLLib.GetTableName(type);
            }
            return connection.Query<int>(SQLLib.CountSQL(tableName, conditions, countColumn)).FirstOrDefault();
        }
    }
 }
